package com.zb.daoimpl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.zb.dao.BaseDao;
import com.zb.dao.YhDao;
import com.zb.entity.Car;
import com.zb.entity.Msi;
import com.zb.entity.Store;
import com.zb.entity.Yh;

public class YhDaoImpl implements YhDao{
	private BaseDao db=new BaseDao();
	public List<Yh> getYhList(Integer index, Integer size, String carnum) {
		List<Yh> list=new ArrayList<Yh>();
		String sql="SELECT c.id, c.`CarNum`,c.`CarName`,c.`CarAir`,yh.`StickExp`,yh.`StickeDate`,yh.`StickMoney`,sto.`StoreName`,yh.`Remake`\r\n" + 
				"FROM CAR AS c JOIN yh AS yh  ON c.`id`=yh.`Id` JOIN store AS sto ON yh.`StickShop`=sto.`id`";
		if(carnum!=null&&"".equals(carnum)) {
			sql+=" and carnum like '%" + carnum + "%'";
		}
		sql+=" order by carnum limit ?,?";
		
		
		ResultSet rs=db.query(sql, (index-1)*size,size);
		try {
			while(rs.next()) {
				Car c=new Car();
				Yh y=new Yh();
				Msi m=new Msi();
				Store s=new Store();
				c.setId(rs.getInt(1));
				c.setCarnum(rs.getString(2));
				c.setCarname(rs.getString(3));
				c.setCarair(rs.getString(4));
				
				y.setStickExp(rs.getString(5));
				y.setStickDate(rs.getString(6));
				y.setStickMoney(rs.getString(7));
				s.setStoreName(rs.getString(8));
				y.setRemake(rs.getString(9));
				y.setCar(c);
				y.setStore(s);
				list.add(y);
				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			db.close();
		}
		return list;
	}

	public int getYhCount(String name) {
		String sql="SELECT COUNT(*) FROM CAR AS c JOIN yh AS yh  ON c.`id`=yh.`Id` JOIN store AS sto ON yh.`StickShop`=sto.`id`";
		ResultSet rs=db.query(sql);
		int count =0;
		try {
			if(rs.next()) {
				count=rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			db.close();
		}
		return count;
	}

	public void addYh(Yh y) {
		String sql="insert yh into values(null,?,?,NOW(),?,?,?)";
		db.update(sql, y.getStickExp(),y.getMsi().getId(),y.getStickMoney(),y.getStore().getId(),y.getRemake());
		db.close();
	}

	public void update(Yh y) {
		// TODO Auto-generated method stub
		String sql="update yh set stickExp=?,Stickname=?,StickeDate=NOW(),StickMoney=?,Remake=? where id=?";
		db.update(sql, y.getStickExp(),1,y.getStickMoney(),y.getRemake(),y.getCar().getId());
		db.close();
	}

	public void delYh(int id) {
		// TODO Auto-generated method stub
		String sql="delete from yh where id=?";
		db.update(sql, id);
		db.close();
	}

	public Yh getYhById(int id) {
		String sql="select*from yh where id=?";
		ResultSet rs=db.query(sql, id);
		try {
			if(rs.next()) {
				Car c=new Car();
				Yh y=new Yh();
				Msi m=new Msi();
				Store s=new Store();
				c.setId(rs.getInt(1));
				y.setStickExp(rs.getString(2));
				m.setId(rs.getInt(3));
				y.setStickDate(rs.getString(4));
				y.setStickMoney(rs.getString(5));
				s.setId(rs.getInt(6));
				y.setRemake(rs.getString(7));
				
				y.setMsi(m);
				y.setStore(s);
				return y;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			db.close();
		}
		return null;
	}

}
